<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use app\index\controller\Login;
use think\Db;
use app\index\service\ExcelService;

class BookTable extends BaseTable
{
    protected static $view = 'V_ASSET_BOOK';
    protected static $table = 'ASSET_BOOK';
    protected static $pk = 'BOOK_NO';
    protected static $configTable = 'ASSET_BOOK_CONFIG';

    static public function table(){
        $sql = "(
            select 
            b.book_no
            ,to_char(b.input_date,'yyyy-mm-dd') input_date
            ,b.check_land_no
            ,b.asset_type_id
            ,d1.cname94 ASSET_TYPE
            ,b.book_name
            ,b.book_unit
            ,b.book_num
            ,b.ANTIQUERANK_ID
            ,b.SAVE_ROOM
            ,b.SAVE_AREA
            ,b.VALUETYPE_ID
            ,d2.VALUETYPE
            ,b.BOOK_FEE
            ,b.BOOK_VIRTUAL_FEE
            ,b.FINANCE_GET_FEE
            ,b.BUSINESS_GET_FEE
            ,b.BUSINESS_BUDGET_GET_FEE
            ,b.OTHER_FEE
            ,b.OTHER_FINANCE_FEE
            ,b.ACCOUNT_CERT_NO
            ,b.PURCHASE_ID
            ,d3.PURCHASE
            ,b.STATUS_ID
            ,d6.STATUS
            ,b.USEDIRECTION_ID
            ,d5.USEDIRECTION
            ,to_char(b.GET_DATE,'yyyy-mm-dd') GET_DATE
            ,b.GETTYPE_ID
            ,d4.GETTYPE
            ,b.PROPERTYRIGHT_ID
            ,b.COLLEGE_ID
            ,d7.COLLEGE USE_COLLEGE
            ,b.MGR_COLLEGE_ID
            ,d10.COLLEGE MGR_COLLEGE
            ,b.USER_MGR_ID
            ,d9.NAME MGR_USER_NAME
            ,b.USE_NAME
            ,d8.NAME USER_NAME
            ,b.REMARK
            ,b.USER_ID
            ,to_char(b.OPER_DATETIME,'yyyy-mm-dd') OPER_DATETIME
            ,b.BUDGET_NO
            ,b.USE_LIFE
            ,b.SELLER
            ,to_char(b.REPAIR_DEADLINE,'yyyy-mm-dd') REPAIR_DEADLINE
            ,b.ENTER_TYPE_ID
            ,to_char(b.ENTER_DATE,'yyyy-mm-dd') ENTER_DATE
            ,to_char(b.START_USE_DATE,'yyyy-mm-dd') START_USE_DATE
            ,b.SAVE_YEAR
            ,b.FILE_NO
            ,b.PRESS_NAME
            ,to_char(b.PRESS_DATE,'yyyy-mm-dd') PRESS_DATE
            ,b.DEPRECIATE_STATUS_ID
            ,b.DEPRECIATE_WAY
            ,b.DEPRECIATE_LIFE
            ,b.REDUCE_PRE
            ,b.DEPRECIATE_PER_MON
            ,b.DEPRECIATE_PERCENTAGE
            ,b.DEPRECIATED_MONTH
            ,b.ACC_DEPRECIATE
            ,b.NON_FINANCE_FEE
            ,b.AVG_FEE
            from app_asset.asset_book b
            left join app_asset.ASSET_DICT_TYPE94TO2010 d1 on d1.ccode94=b.asset_type_id
            left join app_asset.ASSET_DICT_VALUETYPE d2 on d2.valuetype_id=b.VALUETYPE_ID
            left join app_asset.ASSET_DICT_PURCHASE d3 on d3.PURCHASE_ID=b.PURCHASE_ID
            left join app_asset.ASSET_DICT_GETTYPE d4 on d4.GETTYPE_ID=b.GETTYPE_ID
            left join app_asset.ASSET_DICT_USEDIRECTION d5 on d5.USEDIRECTION_ID=b.USEDIRECTION_ID
            left join app_asset.ASSET_DICT_STATUS d6 on d6.STATUS_ID=b.STATUS_ID
            left join app_asset.DICT_COLLEGE d7 on d7.COLLEGE_ID=b.COLLEGE_ID
            left join app_asset.EMPLOYEE d8 on d8.STAFF_ID=b.USE_NAME
            left join app_asset.EMPLOYEE d9 on d9.STAFF_ID=b.USER_MGR_ID
            left join app_asset.DICT_COLLEGE d10 on d10.COLLEGE_ID=b.MGR_COLLEGE_ID
        )";
        return Db::table($sql);
    }

    static public function add($data){
        $data[self::$pk] = static::getNextId();
        $data['INPUT_USER_ID'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'REPAIR_DEADLINE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'PRESS_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
    }

    static public function addImport($data){
        $data['INPUT_USER_ID'] = Login::getUserId();
        $data['INPUT_TIME'] = date('Y-m-d');

        self::toDate($data,'GET_DATE');
        self::toDate($data,'REPAIR_DEADLINE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'PRESS_DATE');
        self::toDate($data,'INPUT_TIME');
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        self::toDate($data,'GET_DATE');
        self::toDate($data,'REPAIR_DEADLINE');
        self::toDate($data,'ENTER_DATE');
        self::toDate($data,'START_USE_DATE');
        self::toDate($data,'PRESS_DATE');
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function getConfigAdd(){
        return Db::table(static::$configTable)->where(['ADD_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigEdit(){
        return Db::table(static::$configTable)->where(['EDIT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigPrint(){
        return Db::table(static::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR('.static::$pk.',5,4)) as NUM')
            ->where([['SUBSTR('.static::$pk.',1,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return $year.sprintf("%04d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'图书编号','field'=>'BOOK_NO'],
            ['title'=>'资产分类代码','field'=>'ASSET_TYPE'],
            ['title'=>'资产名称','field'=>'BOOK_NAME'],
            ['title'=>'原资产编号','field'=>'OLD_BOOK_NO'],
            ['title'=>'存放地点','field'=>'SAVE_ROOM'],
            ['title'=>'采购组织形式','field'=>'PURCHASE'],
            ['title'=>'预算项目编号','field'=>'BUDGET_NO'],
            ['title'=>'取得方式','field'=>'GETTYPE'],
            ['title'=>'取得日期','field'=>'GET_DATE'],
            ['title'=>'预计使用年限（月）','field'=>'USE_LIFE'],
            ['title'=>'销售商','field'=>'SELLER'],
            ['title'=>'保修截止日期','field'=>'REPAIR_DEADLINE'],
            ['title'=>'数量','field'=>'BOOK_NUM'],
            ['title'=>'入账形式','field'=>'ENTER_TYPE'],
            ['title'=>'价值形式','field'=>'VALUETYPE'],
            ['title'=>'价值','field'=>'BOOK_FEE'],
            ['title'=>'财政性资金','field'=>'FINANCE_GET_FEE'],
            ['title'=>'非财政性资金','field'=>'NON_FINANCE_FEE'],
            ['title'=>'均价','field'=>'AVG_FEE'],
            ['title'=>'会计凭证号','field'=>'ACCOUNT_CERT_NO'],
            ['title'=>'财务入账日期','field'=>'ENTER_DATE'],
            ['title'=>'使用状态','field'=>'STATUS_ID'],
            ['title'=>'投入使用日期','field'=>'START_USE_DATE'],
            ['title'=>'保存年限','field'=>'SAVE_YEAR'],
            ['title'=>'使用方向','field'=>'USEDIRECTION'],
            ['title'=>'使用单位','field'=>'COLLEGE_ID'],
            ['title'=>'管理单位','field'=>'MGR_COLLEGE_ID'],
            ['title'=>'使用人','field'=>'USE_NAME'],
            ['title'=>'档案号','field'=>'FILE_NO'],
            ['title'=>'出版社','field'=>'PRESS_NAME'],
            ['title'=>'出版日期','field'=>'PRESS_DATE'],
            ['title'=>'折旧状态','field'=>'DEPRECIATE'],
            ['title'=>'折旧方法','field'=>'DEPRECIATE_WAY'],
            ['title'=>'折旧年限（月）','field'=>'DEPRECIATE_LIFE'],
            ['title'=>'减值准备','field'=>'REDUCE_PRE'],
            ['title'=>'月折旧额','field'=>'DEPRECIATE_PER_MON'],
            ['title'=>'残值率','field'=>'DEPRECIATE_PERCENTAGE'],
            ['title'=>'已提折旧月数','field'=>'DEPRECIATED_MONTH'],
            ['title'=>'累计折旧','field'=>'ACC_DEPRECIATE'],
            ['title'=>'备注','field'=>'REMARK'],
            ['title'=>'制单人','field'=>'INPUT_NAME'],
            ['title'=>'制单时间','field'=>'INPUT_TIME'],
            ['title'=>'隶属单位','field'=>'BELONG'],
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const BOOK_FIELD_LIST = [
        "BOOK_NO","ASSET_TYPE_ID","BOOK_NAME","OLD_BOOK_NO","SAVE_ROOM","PURCHASE_ID","BUDGET_NO","GETTYPE_ID","GET_DATE","USE_LIFE","SELLER","REPAIR_DEADLINE","BOOK_NUM","ENTER_TYPE_ID","ENTER_TYPE_ID","BOOK_FEE","FINANCE_GET_FEE","NON_FINANCE_FEE","AVG_FEE","ACCOUNT_CERT_NO","ENTER_DATE","STATUS_ID","START_USE_DATE","SAVE_YEAR","USEDIRECTION_ID","COLLEGE_ID","MGR_COLLEGE_ID","USE_NAME","FILE_NO","PRESS_NAME","PRESS_DATE","DEPRECIATE_STATUS_ID","DEPRECIATE_WAY","DEPRECIATE_LIFE","REDUCE_PRE","DEPRECIATE_PER_MON","DEPRECIATE_PERCENTAGE","DEPRECIATED_MONTH","ACC_DEPRECIATE","REMARK","BELONG_ID"
    ];

    public static function getSessionConditions() {
        $condition = null;
        $listCondition = Login::getListConditionBook();
        if (!empty($listCondition['BOOK_NO'])) {
            $value = $listCondition['BOOK_NO'];
            $condition[] = ['BOOK_NO','like',"$value%"];
        }
        if (!empty($listCondition['BOOK_NAME'])) {
            $value = $listCondition['BOOK_NAME'];
            $condition[] = ['BOOK_NAME','like',"%$value%"];
        }
        if (!empty($listCondition['BUDGET_NO'])) {
            $value = $listCondition['BUDGET_NO'];
            $condition[] = ['BUDGET_NO','like',"%$value%"];
        }
        if (!empty($listCondition['PRESS_NAME'])) {
            $value = $listCondition['PRESS_NAME'];
            $condition[] = ['PRESS_NAME','like',"%$value%"];
        }
        if (!empty($listCondition['ASSET_TYPE_ID'])) {
            $value = $listCondition['ASSET_TYPE_ID'];
            $condition[] = ['ASSET_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['PURCHASE_ID'])) {
            $value = $listCondition['PURCHASE_ID'];
            $condition[] = ['PURCHASE_ID','=',$value];
        }
        if (!empty($listCondition['GETTYPE_ID'])) {
            $value = $listCondition['GETTYPE_ID'];
            $condition[] = ['GETTYPE_ID','=',$value];
        }
        if (!empty($listCondition['ENTER_TYPE_ID'])) {
            $value = $listCondition['ENTER_TYPE_ID'];
            $condition[] = ['ENTER_TYPE_ID','=',$value];
        }
        if (!empty($listCondition['VALUETYPE_ID'])) {
            $value = $listCondition['VALUETYPE_ID'];
            $condition[] = ['VALUETYPE_ID','=',$value];
        }
        if (!empty($listCondition['STATUS_ID'])) {
            $value = $listCondition['STATUS_ID'];
            $condition[] = ['STATUS_ID','=',$value];
        }
        if (!empty($listCondition['USEDIRECTION_ID'])) {
            $value = $listCondition['USEDIRECTION_ID'];
            $condition[] = ['STATUSEDIRECTION_IDUS_ID','=',$value];
        }
        if (!empty($listCondition['COLLEGE_ID'])) {
            $value = $listCondition['COLLEGE_ID'];
            $condition[] = ['COLLEGE_ID','=',$value];
        }
        if (!empty($listCondition['MGR_COLLEGE_ID'])) {
            $value = $listCondition['MGR_COLLEGE_ID'];
            $condition[] = ['MGR_COLLEGE_ID','=',$value];
        }
        if (!empty($listCondition['BELONG_ID']) || $listCondition['BELONG_ID']==='000') {
            $value = $listCondition['BELONG_ID'];
            $condition[] = ['BELONG_ID','=',$value];
        }

        if (!empty($listCondition['BOOK_FEE_START'])) {
            $value = $listCondition['BOOK_FEE_START'];
            $condition[] = ['BOOK_FEE_START','>=',$value];
        }
        if (!empty($listCondition['BOOK_FEE_END'])) {
            $value = $listCondition['BOOK_FEE_END'];
            $condition[] = ['BOOK_FEE_END','<=',$value];
        }

        if (!empty($listCondition['GET_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','>=',$value];
        }
        if (!empty($listCondition['GET_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['GET_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['GET_DATE','<=',$value];
        }

        if (!empty($listCondition['ENTER_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['ENTER_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['ENTER_DATE','>=',$value];
        }
        if (!empty($listCondition['ENTER_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['ENTER_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['ENTER_DATE','<=',$value];
        }

        if (!empty($listCondition['START_USE_DATE_START'])) {
            $value = Db::raw("to_date('{$listCondition['START_USE_DATE_START']}','yyyy-mm-dd')");
            $condition[] = ['START_USE_DATE','>=',$value];
        }
        if (!empty($listCondition['START_USE_DATE_END'])) {
            $value = Db::raw("to_date('{$listCondition['START_USE_DATE_END']}','yyyy-mm-dd')");
            $condition[] = ['START_USE_DATE','<=',$value];
        }

        return $condition;
    }
}